-- 2020-04-02T09:51:39.497Z
-- I forgot to set the DICTIONARY_ID_COMMENTS System Configurator
UPDATE AD_TreeNodeMM SET Parent_ID=540754, SeqNo=0, Updated=now(), UpdatedBy=100 WHERE  Node_ID=541452 AND AD_Tree_ID=10
;

-- 2020-04-02T10:09:33.651Z
-- I forgot to set the DICTIONARY_ID_COMMENTS System Configurator
UPDATE AD_Process SET Value='CurrentVsPreviousPricelistComparison', Name='Current Vs Last Pricelist Comparison',Updated=TO_TIMESTAMP('2020-04-02 13:09:33','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Process_ID=584680
;

-- 2020-04-02T10:09:33.672Z
-- I forgot to set the DICTIONARY_ID_COMMENTS System Configurator
UPDATE AD_Menu SET IsActive='Y', Name='Current Vs Last Pricelist Comparison', Description='',Updated=TO_TIMESTAMP('2020-04-02 13:09:33','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Menu_ID=541452
;

-- 2020-04-02T10:09:55.940Z
-- I forgot to set the DICTIONARY_ID_COMMENTS System Configurator
UPDATE AD_Process SET Name='Current Vs Previous Pricelist Comparison',Updated=TO_TIMESTAMP('2020-04-02 13:09:55','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Process_ID=584680
;

-- 2020-04-02T10:09:55.943Z
-- I forgot to set the DICTIONARY_ID_COMMENTS System Configurator
UPDATE AD_Menu SET IsActive='Y', Name='Current Vs Previous Pricelist Comparison', Description='',Updated=TO_TIMESTAMP('2020-04-02 13:09:55','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Menu_ID=541452
;

-- 2020-04-02T10:12:45.749Z
-- I forgot to set the DICTIONARY_ID_COMMENTS System Configurator
UPDATE AD_Element_Trl SET PrintName='Current vs Previous Pricelist Comparison Report', Name='Current vs Previous Pricelist Comparison Report',Updated=TO_TIMESTAMP('2020-04-02 13:12:45','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Language='nl_NL' AND AD_Element_ID=577626
;

-- 2020-04-02T10:12:45.751Z
-- I forgot to set the DICTIONARY_ID_COMMENTS System Configurator
/* DDL */  select update_TRL_Tables_On_AD_Element_TRL_Update(577626,'nl_NL') 
;


-- 2020-04-02T13:48:52.860Z
-- I forgot to set the DICTIONARY_ID_COMMENTS System Configurator
UPDATE AD_Process_Para SET DefaultValue='Y',Updated=TO_TIMESTAMP('2020-04-02 16:48:52','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Process_Para_ID=541789
;




DROP FUNCTION IF EXISTS report.Current_Vs_Previous_Pricelist_Comparison_Report(p_C_BPartner_ID numeric, p_C_BP_Group_ID numeric, p_IsSoTrx text, p_AD_Language text)
;

CREATE OR REPLACE FUNCTION report.Current_Vs_Previous_Pricelist_Comparison_Report(p_C_BPartner_ID numeric = NULL,
                                                                                  p_C_BP_Group_ID numeric = NULL,
                                                                                  p_IsSoTrx       text = 'Y',
                                                                                  p_AD_Language   TEXT = 'en_US')
    RETURNS TABLE
            (
                bp_value                  text,
                bp_name                   text,
                ProductCategory           text,
                M_Product_ID              integer,
                value                     text,
                CustomerProductNumber     text,
                ProductName               text,
                IsSeasonFixedPrice        text,
                ItemProductName           text,
                qtycuspertu               numeric,
                packingmaterialname       text,
                pricestd                  numeric,
                altpricestd               numeric,
                hasaltprice               integer,
                uomsymbol                 text,
                uom_x12de355              text,
                Attributes                text,
                m_productprice_id         integer,
                m_attributesetinstance_id integer,
                m_hu_pi_item_product_id   integer,
                currency                  text,
                currency2                 text,
                validFromPLV1             timestamp,
                validFromPLV2             timestamp,
                namePLV1                  text,
                namePLV2                  text,
                c_bpartner_location_id    numeric,
                AD_Org_ID                 numeric
            )
AS
$$
WITH PriceListVersionsByValidFrom AS
         (
             SELECT t.*
             FROM (SELECT --
                          plv.c_bpartner_id,
                          plv.m_pricelist_version_id,
                          plv.validfrom,
                          plv.name,
                          row_number() OVER (PARTITION BY plv.c_bpartner_id ORDER BY plv.validfrom DESC, plv.m_pricelist_version_id DESC) rank
                   FROM Report.Fresh_PriceList_Version_Val_Rule plv
                   WHERE TRUE
                     AND plv.validfrom <= now()
                     AND plv.issotrx = p_IsSoTrx
                     AND (p_C_BPartner_ID IS NULL OR plv.c_bpartner_id = p_C_BPartner_ID)
                     AND (p_C_BP_Group_ID IS NULL OR plv.c_bpartner_id IN (SELECT DISTINCT b.c_bpartner_id FROM c_bpartner b WHERE b.c_bp_group_id = p_C_BP_Group_ID))
                   ORDER BY TRUE,
                            plv.validfrom DESC,
                            plv.m_pricelist_version_id DESC) t
             WHERE t.rank <= 2
         ),
     currentAndPreviousPLV AS
         (
             -- implementation detail: all these sub-selects would be better implemented with a pivot. Unfortunately i cant understand how pivots work.
             SELECT DISTINCT --
                             plvv.c_bpartner_id,
                             (SELECT plvv2.m_pricelist_version_id FROM PriceListVersionsByValidFrom plvv2 WHERE plvv2.rank = 1 AND plvv2.c_bpartner_id = plvv.c_bpartner_id) PLV1_ID,
                             (SELECT plvv2.m_pricelist_version_id FROM PriceListVersionsByValidFrom plvv2 WHERE plvv2.rank = 2 AND plvv2.c_bpartner_id = plvv.c_bpartner_id) PLV2_ID,
                             (SELECT plvv2.validfrom FROM PriceListVersionsByValidFrom plvv2 WHERE plvv2.rank = 1 AND plvv2.c_bpartner_id = plvv.c_bpartner_id)              validFromPLV1,
                             (SELECT plvv2.validfrom FROM PriceListVersionsByValidFrom plvv2 WHERE plvv2.rank = 2 AND plvv2.c_bpartner_id = plvv.c_bpartner_id)              validFromPLV2,
                             (SELECT plvv2.name FROM PriceListVersionsByValidFrom plvv2 WHERE plvv2.rank = 1 AND plvv2.c_bpartner_id = plvv.c_bpartner_id)                   namePLV1,
                             (SELECT plvv2.name FROM PriceListVersionsByValidFrom plvv2 WHERE plvv2.rank = 2 AND plvv2.c_bpartner_id = plvv.c_bpartner_id)                   namePLV2
             FROM PriceListVersionsByValidFrom plvv
             ORDER BY plvv.c_bpartner_id
         ),
     result AS
         (
             SELECT t.*,
                    plv.validFromPLV1,
                    plv.validFromPLV2,
                    plv.namePLV1,
                    plv.namePLV2,
                    (SELECT bpl.c_bpartner_location_id FROM c_bpartner_location bpl WHERE bpl.c_bpartner_id = plv.c_bpartner_id ORDER BY bpl.isbilltodefault DESC LIMIT 1) c_bpartner_location_id,
                    (SELECT plv2.ad_org_id FROM m_pricelist_version plv2 WHERE plv2.m_pricelist_version_id = plv.PLV1_ID)                                AD_Org_ID
             FROM currentAndPreviousPLV plv
                      INNER JOIN LATERAL report.fresh_PriceList_Details_Report(
                     plv.c_bpartner_id,
                     plv.PLV1_ID,
                     plv.PLV2_ID,
                     p_AD_Language
                 ) AS t ON TRUE
         )
SELECT --
       r.bp_value,
       r.bp_name,
       r.productcategory,
       r.m_product_id,
       r.value,
       r.customerproductnumber,
       r.productname,
       r.isseasonfixedprice::text,
       r.itemproductname,
       r.qtycuspertu,
       r.packingmaterialname,
       r.pricestd,
       r.altpricestd,
       r.hasaltprice,
       r.uomsymbol,
       r.uom_x12de355,
       r.attributes,
       r.m_productprice_id,
       r.m_attributesetinstance_id,
       r.m_hu_pi_item_product_id,
       r.currency::text,
       r.currency2::text,
       r.validFromPLV1,
       r.validFromPLV2,
       r.namePLV1,
       r.namePLV2,
       r.c_bpartner_location_id,
       r.AD_Org_ID
FROM result r
ORDER BY TRUE,
         r.bp_value,
         r.value
$$
    LANGUAGE sql STABLE
;
